﻿'========================================================================
'功能:入網型態資料庫存取物件
'作者:andy
'日期: 2011/3/1
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data


Namespace Telecom.DAO
    Public Class NetworkTypeDAO
        Inherits OAMS.DAO.BaseDAO
        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub
        ''' <summary>
        ''' 取得某一廠的入網型態
        ''' </summary>
        ''' <returns>取得某一廠的入網型態</returns>
        ''' <remarks>取得某一廠的入網型態</remarks>
        Public Function GetNetworkType(ByVal network_site As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_site


            Dim sb As New StringBuilder()

            sb.AppendLine(" select 0 as canModi,convert(varchar(10),CrtDate,111) as CrtDate,convert(varchar(10),ModiDate,111) as ModiDate, A.* ,B.emp_name as CrtName,C.emp_name as ModiName from TEL_NetworkType_C  A left join  AUOREF.dbo.emp_data_all  B  on A.CrtUID=B.emp_no collate Chinese_Taiwan_Stroke_CI_AS left join  AUOREF.dbo.emp_data_all  C on A.ModiUID=C.emp_no collate Chinese_Taiwan_Stroke_CI_AS where network_site=@network_site")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


        Public Function GetNetworkTypeInsert(ByVal network_site As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_site


            Dim sb As New StringBuilder()
            sb.AppendLine("  select 0 as FirstPay, 0 as ChkOA,1  as canModi ,'' as network_no,'' as network_name,'' as  network_site ,'' as network_desc,0  as  NeedApprove,'' as CrtUID,null as  CrtDate,'' as ModiUID,null as  ModiDate,'' as CrtName,'' as ModiName,'' as Message ")
            sb.AppendLine(" union   select FirstPay,ChkOA, 0 as canModi ,network_no,network_name,network_site ,network_desc,NeedApprove,CrtUID,convert(varchar(10),CrtDate,111) as CrtDate,ModiUID,convert(varchar(10),ModiDate,111) as ModiDate ,B.emp_name as CrtName,C.emp_name  as ModiName,A.Message from TEL_NetworkType_C  A left join  AUOREF.dbo.emp_data_all  B  on A.CrtUID=B.emp_no collate Chinese_Taiwan_Stroke_CI_AS left join AUOREF.dbo.emp_data_all  C on A.ModiUID=C.emp_no  collate Chinese_Taiwan_Stroke_CI_AS  where A.network_site=@network_site  order by canModi desc ")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        ''' <summary>
        ''' 新增入網型態資料
        ''' </summary>
        ''' <remarks>新增入網型態資料</remarks>
        Public Sub InsertNetworkType(ByVal network_no As String, ByVal network_name As String, ByVal network_site As String, ByVal network_desc As String, ByVal NeedApprove As Integer, ByVal UID As String, ByVal ChkOA As Boolean, ByVal Message As String, ByVal FirstPay As Boolean)
            Dim sqlParams As SqlParameter() = New SqlParameter(9) {}

            sqlParams(0) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_no
            sqlParams(1) = New SqlParameter("@network_name", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_name
            sqlParams(2) = New SqlParameter("@NeedApprove", SqlDbType.Int)
            sqlParams(2).Value = NeedApprove
            sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = UID
            sqlParams(4) = New SqlParameter("@network_desc", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = network_desc
            sqlParams(5) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(5).Value = network_site
            sqlParams(6) = New SqlParameter("@ChkOA", SqlDbType.Bit)
            sqlParams(6).Value = ChkOA
            sqlParams(7) = New SqlParameter("@Message", SqlDbType.NVarChar, 100)
            sqlParams(7).Value = Message
            sqlParams(8) = New SqlParameter("@FirstPay", SqlDbType.Bit)
            sqlParams(8).Value = FirstPay
            Dim sb As New StringBuilder()
            sb.AppendLine(" insert into  TEL_NetworkType_C(network_no,network_name,NeedApprove,network_desc,network_site,CrtUID,CrtDate,ModiUID,ModiDate,ChkOA,Message,FirstPay) ")
            sb.AppendLine("values(@network_no,@network_name ,@NeedApprove,@network_desc,@network_site,@UID,getdate(),'',null,@ChkOA,@Message,@FirstPay )")

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        ''' <summary>
        ''' 修改入網型態資料
        ''' </summary>  
        ''' <remarks>修改入網型態資料</remarks>
        Public Sub UpdateNetworkType(ByVal network_no As String, ByVal network_name As String, ByVal network_site As String, ByVal network_desc As String, ByVal NeedApprove As Integer, ByVal UID As String, ByVal ChkOA As Boolean, ByVal Message As String, ByVal FirstPay As Boolean)

            Dim sqlParams As SqlParameter() = New SqlParameter(9) {}
            sqlParams(0) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_no
            sqlParams(1) = New SqlParameter("@network_name", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_name
            sqlParams(2) = New SqlParameter("@NeedApprove", SqlDbType.Int)
            sqlParams(2).Value = NeedApprove
            sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = UID
            sqlParams(4) = New SqlParameter("@network_desc", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = network_desc
            sqlParams(5) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(5).Value = network_site
            sqlParams(6) = New SqlParameter("@ChkOA", SqlDbType.Bit)
            sqlParams(6).Value = ChkOA
            sqlParams(7) = New SqlParameter("@Message", SqlDbType.NVarChar, 100)
            sqlParams(7).Value = Message
            sqlParams(8) = New SqlParameter("@FirstPay", SqlDbType.Bit)
            sqlParams(8).Value = FirstPay
            Dim sb As New StringBuilder()
            sb.AppendLine(" update  TEL_NetworkType_C  set network_name=@network_name,network_desc=@network_desc,NeedApprove=@NeedApprove,ModiUID=@UID ,ModiDate=getdate() ,ChkOA=@ChkOA,Message=@Message,FirstPay=@FirstPay where  network_no=@network_no  and network_site=@network_site")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub


        ''' <summary>
        ''' 檢查入網型態是否存在
        ''' </summary>
        ''' <returns>檢查入網型態是否存在</returns>
        ''' <remarks>檢查入網型態是否存在</remarks>
        Public Function network_noIsExist(ByVal network_no As String, ByVal network_site As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            sqlParams(0) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_no
            sqlParams(1) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_site
            Dim sb As New StringBuilder()
            sb.AppendLine(" select * from TEL_NetworkType_C  where   network_no=@network_no  and network_site=@network_site")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function

        Public Function GetDataBynetwork_no(ByVal network_no As String, ByVal network_site As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            sqlParams(0) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_no
            sqlParams(1) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_site
            Dim sb As New StringBuilder()
            sb.AppendLine(" select * from TEL_NetworkType_C  where   network_no=@network_no  and network_site=@network_site")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        Public Function GetTypeNetworkType(ByVal network_site As String, ByVal network_no As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_site
            sqlParams(1) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_no


            Dim sb As New StringBuilder()

            sb.AppendLine(" select 0 as canModi,convert(varchar(10),A.CrtDate,111) as CrtDate, A.* ,B.emp_name as CrtName, C.[type_name] from TEL_TYPE_NetworkType  A left join  AUOREF.dbo.emp_data_all  B  on A.CrtUID=B.emp_no  collate Chinese_Taiwan_Stroke_CI_AS left join TEL_TYPE_C C on A.type_code=C.type_code and A.network_site=C.suit_site where A.network_site=@network_site  and A.network_no=@network_no")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        Public Function GetTypeNetworkTypeByApplyType(ByVal network_site As String, ByVal type_code As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_site
            sqlParams(1) = New SqlParameter("@type_code", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = type_code


            Dim sb As New StringBuilder()

            sb.AppendLine("select A.* from TEL_NetworkType_C  A inner join TEL_TYPE_NetworkType B on A.network_no =B.network_no  and A.network_site =b.network_site  where A.network_site= @network_site and B.type_code =@type_code")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


        Public Function GetTypeNetworkTypeInsert(ByVal network_site As String, ByVal network_no As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_site
            sqlParams(1) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_no


            Dim sb As New StringBuilder()
            sb.AppendLine(" select 1  as canModi ,'' as  type_code, '' as [type_name],  '' as CrtUID,null as  CrtDate,'' as CrtName ")
            sb.AppendLine("   union select 0 as canModi ,A.type_code,C.[type_name],A.CrtUID,convert(varchar(10),A.CrtDate,111) as CrtDate")
            sb.AppendLine("   ,B.emp_name as CrtName from TEL_TYPE_NetworkType  A left join  AUOREF.dbo.emp_data_all  B  on A.CrtUID=B.emp_no collate Chinese_Taiwan_Stroke_CI_AS  ")
            sb.AppendLine("   left join TEL_TYPE_C C on A.type_code=C.type_code  and A.network_site=C.suit_site where A.network_site=@network_site  and A.network_no =@network_no order by canModi desc ")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


        Public Sub TypeNetworkTypeInsert(ByVal network_site As String, ByVal network_no As String, ByVal type_code As String, ByVal UID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(4) {}

            sqlParams(0) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_site
            sqlParams(1) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_no

            sqlParams(2) = New SqlParameter("@type_code", SqlDbType.NVarChar, 100)
            sqlParams(2).Value = type_code
            sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = UID
            Dim sb As New StringBuilder()
            sb.AppendLine("  insert  TEL_TYPE_NetworkType (network_site,network_no,type_code,CrtUID ,CrtDate,ModiUID) values  ")
            sb.AppendLine("  (@network_site,@network_no,@type_code,@UID ,getDate(),'' )")

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub

        Public Function TelTypenetwork_noIsExist(ByVal network_no As String, ByVal network_site As String, ByVal type_code As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(3) {}
            sqlParams(0) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_no
            sqlParams(1) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_site
            sqlParams(2) = New SqlParameter("@type_code", SqlDbType.NVarChar, 100)
            sqlParams(2).Value = type_code
            Dim sb As New StringBuilder()
            sb.AppendLine(" select * from TEL_TYPE_NetworkType  where   network_no=@network_no  and network_site=@network_site  and type_code=@type_code ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function

        Public Sub DeleteTypenetwork_no(ByVal network_no As String, ByVal network_site As String, ByVal type_code As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(3) {}
            sqlParams(0) = New SqlParameter("@network_no", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = network_no
            sqlParams(1) = New SqlParameter("@network_site", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = network_site
            sqlParams(2) = New SqlParameter("@type_code", SqlDbType.NVarChar, 100)
            sqlParams(2).Value = type_code
            Dim sb As New StringBuilder()
            sb.AppendLine(" delete  from TEL_TYPE_NetworkType  where   network_no=@network_no  and network_site=@network_site  and type_code=@type_code ")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
    End Class


End Namespace


